package cn.dida.dao;

import cn.dida.model.Pageable;
import cn.dida.model.Product;
import cn.dida.model.ScanStatics;
import cn.dida.model.stastic.ProductScanCountByDate;
import org.apache.commons.lang3.ArrayUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 * Created by zhangzhiling on 2017/8/10.
 */
@Repository
public class DataStatisticsDao extends DaoBase {


    /**
     * 获取产品列表
     * @param companyId
     * @return
     */
    public List<Product>  getProList(Integer companyId){
        StringBuffer sqlStastic = new StringBuffer("select * from product where company_id = " + companyId);

        List<Product> result = jdbcTemplate.query(sqlStastic.toString(), new BeanPropertyRowMapper<>(Product.class));
        return result;
    }
    /**
     * 扫描量/用户量，按天显示
     * @param productId
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public  List<Map<String,Object>> dataCountByDay(Integer companyId,String productId, String  timeLimitStart, String timeLimitEnd) {

        //step2:统计
        Object[] step2_args = {};
        StringBuffer sqlStastic = new StringBuffer("SELECT a.year,a.month,a.vdate1," +
                "IFNULL(e.SCANCOUNT,0) 'scanCount',IFNULL(e.PSNCOUNT,0) 'psnCount'" +
                " FROM dim_calendar a left join" );
        if(productId != null &&  productId != ""){
            sqlStastic.append(" ETL_FACT_DALIY e on e.VDATE=a.vdate  and e.PRODUCT_ID = ?");
            step2_args = ArrayUtils.add(step2_args, productId);
        }else{
            sqlStastic.append(" ETL_CORP_DALIY e on e.VDATE=a.vdate ");
        }
        sqlStastic.append(" and e.COMPANY_ID = ?");
        step2_args = ArrayUtils.add(step2_args, companyId);

        StringBuffer step2_sqlWhere = new StringBuffer(" where 1= 1");

        if (timeLimitStart != null) {
            step2_sqlWhere.append(" and a.vdate >= ?");
            step2_args = ArrayUtils.add(step2_args, timeLimitStart);
        }
        if (timeLimitEnd != null) {
            step2_sqlWhere.append(" and a.vdate <= ?");
            step2_args = ArrayUtils.add(step2_args, timeLimitEnd);
        }


        // 查询统计数据
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlStastic.append(step2_sqlWhere).append("  order by a.vdate").toString(), step2_args);
        return result;
    }

    /**
     * 扫描量/用户量  按月显示
     * @param productId
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public  List<Map<String,Object>> dataCountByMonth(Integer companyId, String productId, String timeLimitStart, String timeLimitEnd) {

        //step2:统计
        Object[] step2_args = {};
        StringBuffer sqlStastic = new StringBuffer("select a.* ,IFNULL(e.SCANCOUNT,0) 'scanCount',IFNULL(e.PSNCOUNT,0) 'psnCount' " +
                "from (SELECT a.year,a.month, left(a.vdate,6) 'vdate1' from dim_calendar a ");
        StringBuffer sqlStastic1 = new StringBuffer(" group by a.year,a.month )a left join");
        if(productId != null &&  productId != ""){
            sqlStastic1.append(" ETL_FACT_MONTHLY e on e.YEARMONTH = a.vdate1 and e.PRODUCT_ID = " +productId);
        }else{
            sqlStastic1.append(" ETL_CORP_MONTHLY e on e.YEARMONTH = a.vdate1 ");
        }

        sqlStastic1.append(" and e.COMPANY_ID = " + companyId);
//        step2_args = ArrayUtils.add(step2_args, companyId);

        StringBuffer step2_sqlWhere = new StringBuffer("  where  1= 1");

        if (timeLimitStart != null) {
            step2_sqlWhere.append(" and a.vdate >= ?");
            step2_args = ArrayUtils.add(step2_args, timeLimitStart);
        }
        if (timeLimitEnd != null) {
            step2_sqlWhere.append(" and a.vdate <= ?");
            step2_args = ArrayUtils.add(step2_args, timeLimitEnd);
        }


        // 查询统计数据
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlStastic.append(step2_sqlWhere).append(sqlStastic1).append(" order by a.vdate1;").toString(), step2_args);
        return result;
    }

    /**
     * 新增用户 /按天
     * @param productId
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public  List<Map<String,Object>> newOpenCountByDay(Integer companyId,String productId, String timeLimitStart, String timeLimitEnd){

        Object[] step2_args = {};

        StringBuffer sqlStastic ;
        if(productId != null && !productId .equals("") && !productId.equals("null")){
            sqlStastic = new StringBuffer("SELECT a.year , a.month, a.vdate1,IFNULL(c.PSNADDNUM,0) scanpan FROM dim_calendar a" +
                    " left join ETL_WEIXIN_PSNSUMBYPROD c ON a.vdate=c.VDATE and c.PRODUCT_ID =" +productId);
        }else{
            sqlStastic = new StringBuffer("SELECT a.year , a.month, a.vdate1,IFNULL(c.PSNADDNUM,0) scanpan FROM dim_calendar a" +
                    " left join ETL_WEIXIN_PSNSUMBYCORP c ON a.vdate=c.VDATE  " );
        }


        StringBuffer step2_sqlWhere = new StringBuffer(" and c.company_id  = ? where 1=1");
        step2_args = ArrayUtils.add(step2_args, companyId);

        if (timeLimitStart != null) {
            step2_sqlWhere.append(" and a.vdate >= ?");
            step2_args = ArrayUtils.add(step2_args, timeLimitStart);
        }
        if (timeLimitEnd != null) {
            step2_sqlWhere.append(" and a.vdate <= ?");
            step2_args = ArrayUtils.add(step2_args, timeLimitEnd);
        }

        // 查询统计数据
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlStastic.append(step2_sqlWhere).append(" order by a.vdate").toString(), step2_args);
        return result;
    }

    /**
     * 新增用户/按月
     * @param productId
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public  List<Map<String,Object>> newOpenCountByMonth(Integer companyId,String productId, String timeLimitStart, String timeLimitEnd){

        Object[] step2_args = {};

        StringBuffer sqlStastic ;
        if(productId != null && !productId .equals("") && !productId.equals("null")){
            sqlStastic = new StringBuffer("SELECT a.year , a.month," +
                    " sum(IFNULL(c.PSNADDNUM,0) )scanpan FROM dim_calendar a left join ETL_WEIXIN_PSNSUMBYPROD c ON a.vdate=c.VDATE" +
                    " and c.PRODUCT_ID =" +productId);
        }else{
            sqlStastic = new StringBuffer("SELECT a.year , a.month, " +
                    " sum(IFNULL(c.PSNADDNUM,0) )scanpan FROM dim_calendar a left join ETL_WEIXIN_PSNSUMBYCORP c ON a.vdate=c.VDATE" );
        }


        StringBuffer step2_sqlWhere = new StringBuffer(" and c.company_id  = ? where 1=1");
        step2_args = ArrayUtils.add(step2_args, companyId);

        if (timeLimitStart != null) {
            step2_sqlWhere.append(" and a.vdate >= ?");
            step2_args = ArrayUtils.add(step2_args, timeLimitStart);
        }
        if (timeLimitEnd != null) {
            step2_sqlWhere.append(" and a.vdate <= ?");
            step2_args = ArrayUtils.add(step2_args, timeLimitEnd);
        }

        // 查询统计数据
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlStastic.append(step2_sqlWhere).append(" group by a.year, a.month order by a.vdate").toString(), step2_args);
        return result;
    }

    /**
     * 获取月/日统计列表数据   /按天
     * @param companyId
     * @param productId
     * @param timeLimitStart
     * @param timeLimitEnd
     * @param page
     * @return
     */
    public Pageable<Map<String,Object>> getStatsDayList(Integer companyId, String productId, String timeLimitStart,
                                                         String timeLimitEnd,Integer page){
        Object[] step2_args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(*) from dim_calendar a ");


        StringBuffer sqlStastic = new StringBuffer("select p.*,n.scanpan from" +
                "(SELECT a.year,a.month,a.vdate1, a.vdate,IFNULL(e.SCANCOUNT,0) 'scanCount'," +
                "IFNULL(e.PSNCOUNT,0) 'psnCount',IFNULL(e.SCANCOUNT_TOTAL,0)  'history_scan_total'," +
                " IFNULL(e.WINCOUNT, 0) 'winCount', IFNULL(e.ENTITYCOUNT,0) 'entityCount',IFNULL(e.REDPACKCOUNT,0) 'redCount'" +
                "  FROM dim_calendar a left join " );
        if (productId != null && productId != "" && !productId.equals("null")) {
            sqlStastic.append(" ETL_FACT_DALIY e on e.VDATE=a.vdate and e.PRODUCT_ID = " + productId);
        }else{
            sqlStastic.append(" ETL_CORP_DALIY e on e.VDATE=a.vdate ");
        }
        sqlStastic.append(" and  e.COMPANY_ID =" + companyId);

        //新增用户
        StringBuffer sqlStastic1=new StringBuffer("  )p join (SELECT a.vdate1,IFNULL(c.PSNADDNUM,0) 'scanpan' " +
                " FROM dim_calendar a left join");
        if (productId != null && productId != "" && !productId.equals("null")) {
            sqlStastic1.append(" ETL_WEIXIN_PSNSUMBYPROD c on a.vdate=c.VDATE and c.PRODUCT_ID = " + productId);
        }else{
            sqlStastic1.append(" ETL_WEIXIN_PSNSUMBYCORP c on a.vdate=c.VDATE ");
        }
        sqlStastic1.append(" and  c.COMPANY_ID =" + companyId);

        StringBuffer step2_sqlWhere = new StringBuffer("  where 1=1");
        if (timeLimitStart != null) {
            step2_sqlWhere.append(" and a.vdate >= " + timeLimitStart);
        }
        if (timeLimitEnd != null) {
            step2_sqlWhere.append(" and a.vdate <= " + timeLimitEnd);
        }

        StringBuffer str = sqlStastic.append(step2_sqlWhere).append(sqlStastic1).append(step2_sqlWhere).append(" ) n on n.vdate1 = p.vdate1  order by p.vdate limit ?,?");
        step2_args = ArrayUtils.add(step2_args, (page - 1) * getPageSize());
        step2_args = ArrayUtils.add(step2_args, getPageSize());

        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(step2_sqlWhere ).toString(), Integer.class);

        List<Map<String,Object>> result = jdbcTemplate.queryForList(str.toString(),step2_args);
        return  new Pageable<>(page, getPageSize(), resultTotal, result);

    }

    /**
     * 获取月/日统计列表数据   /按月
     * @param companyId
     * @param productId
     * @param timeLimitStart
     * @param timeLimitEnd
     * @param page
     * @return
     */
    public Pageable<Map<String,Object>> getStatsMonthList(Integer companyId, String productId, String timeLimitStart,
                                                        String timeLimitEnd,Integer page){
        Object[] step2_args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("SELECT count(*) from ( select a.year,a.month from  dim_calendar a");

        StringBuffer sqlStastic = new StringBuffer("select p.*,n.scanpan from " +
                "(select a.* ,IFNULL(e.SCANCOUNT,0) 'scanCount',IFNULL(e.PSNCOUNT,0) 'psnCount' ," +
                " IFNULL(e.SCANCOUNT_TOTAL,0)  'history_scan_total', " +
                " IFNULL(e.WINCOUNT, 0) 'winCount', IFNULL(e.ENTITYCOUNT,0) 'entityCount',IFNULL(e.REDPACKCOUNT,0) 'redCount' from" +
                " (SELECT a.year,a.month, left(a.vdate,6) 'vdate1' from dim_calendar a  " );

        StringBuffer sqlStastic1= new StringBuffer(" group by a.year,a.month )a  left join");
        if (productId != null && productId != "" && !productId.equals("null")) {
            sqlStastic1.append(" ETL_FACT_MONTHLY e on e.YEARMONTH = a.vdate1  and e.PRODUCT_ID = " + productId);
        }else{
            sqlStastic1.append(" ETL_CORP_MONTHLY e on e.YEARMONTH = a.vdate1 ");
        }
        sqlStastic1.append("  and e.COMPANY_ID =" + companyId  +" )p join");

        //新增用户
        StringBuffer sqlStastic2= new StringBuffer(" (SELECT a.year, a.month, sum(IFNULL(c.PSNADDNUM,0) )scanpan FROM dim_calendar a left join ");
        if (productId != null && productId != "" && !productId.equals("null")) {
            sqlStastic2.append(" ETL_WEIXIN_PSNSUMBYPROD c on a.vdate=c.VDATE  and c.PRODUCT_ID = " + productId);
        }else{
            sqlStastic2.append(" ETL_WEIXIN_PSNSUMBYCORP c on a.vdate=c.VDATE ");
        }
        sqlStastic2.append("  and c.COMPANY_ID =" + companyId  );


        StringBuffer step2_sqlWhere = new StringBuffer("  where 1=1");
        if (timeLimitStart != null) {
            step2_sqlWhere.append(" and a.vdate >= " + timeLimitStart);
        }
        if (timeLimitEnd != null) {
            step2_sqlWhere.append(" and a.vdate <= " + timeLimitEnd);
        }

        StringBuffer str = sqlStastic.append(step2_sqlWhere).append(sqlStastic1).append(sqlStastic2).append(step2_sqlWhere).append(" group by a.year, a.month )n on p.year = n.year and p.month=n.month order by p.vdate1 limit ?,?");
        step2_args = ArrayUtils.add(step2_args, (page - 1) * getPageSize());
        step2_args = ArrayUtils.add(step2_args, getPageSize());

        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(step2_sqlWhere).append( " group by a.year,a.month)b").toString(), Integer.class);

        List<Map<String,Object>> result = jdbcTemplate.queryForList(str.toString(),step2_args);
        return  new Pageable<>(page, getPageSize(), resultTotal, result);

    }

    /**
     * 获取月/日统计列表 导出数据   /按天
     * @param companyId
     * @param productId
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public List<Map<String,Object>> getStatsDayListExport(Integer companyId, String productId, String timeLimitStart,
                                                        String timeLimitEnd){


        StringBuffer sqlStastic = new StringBuffer("select p.*,n.scanpan from" +
                "(SELECT a.year,a.month,a.vdate1, a.vdate,IFNULL(e.SCANCOUNT,0) 'scanCount'," +
                "IFNULL(e.PSNCOUNT,0) 'psnCount',IFNULL(e.SCANCOUNT_TOTAL,0)  'history_scan_total'," +
                " IFNULL(e.WINCOUNT, 0) 'winCount', IFNULL(e.ENTITYCOUNT,0) 'entityCount',IFNULL(e.REDPACKCOUNT,0) 'redCount'" +
                "  FROM dim_calendar a left join " );
        if (productId != null && productId != "" && !productId.equals("null")) {
            sqlStastic.append(" ETL_FACT_DALIY e on e.VDATE=a.vdate and e.PRODUCT_ID = " + productId);
        }else{
            sqlStastic.append(" ETL_CORP_DALIY e on e.VDATE=a.vdate ");
        }
        sqlStastic.append(" and  e.COMPANY_ID =" + companyId);

        //新增用户
        StringBuffer sqlStastic1=new StringBuffer("  )p join (SELECT a.vdate1,IFNULL(c.PSNADDNUM,0) 'scanpan' " +
                " FROM dim_calendar a left join");
        if (productId != null && productId != "" && !productId.equals("null")) {
            sqlStastic1.append(" ETL_WEIXIN_PSNSUMBYPROD c on a.vdate=c.VDATE and c.PRODUCT_ID = " + productId);
        }else{
            sqlStastic1.append(" ETL_WEIXIN_PSNSUMBYCORP c on a.vdate=c.VDATE ");
        }
        sqlStastic1.append(" and  c.COMPANY_ID =" + companyId);

        StringBuffer step2_sqlWhere = new StringBuffer("  where 1=1");
        if (timeLimitStart != null) {
            step2_sqlWhere.append(" and a.vdate >= " + timeLimitStart);
        }
        if (timeLimitEnd != null) {
            step2_sqlWhere.append(" and a.vdate <= " + timeLimitEnd);
        }

        StringBuffer str = sqlStastic.append(step2_sqlWhere).append(sqlStastic1).append(step2_sqlWhere).append(" ) n on n.vdate1 = p.vdate1  order by p.vdate ");
        List<Map<String,Object>> result = jdbcTemplate.queryForList(str.toString());
        return  result;

    }

    /**
     * 获取月/日统计列表 导出数据   /按月
     * @param companyId
     * @param productId
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public List<Map<String,Object>> getStatsMonthListExport(Integer companyId, String productId, String timeLimitStart,
                                                          String timeLimitEnd){

        StringBuffer sqlStastic = new StringBuffer("select p.*,n.scanpan from " +
                "(select a.* ,IFNULL(e.SCANCOUNT,0) 'scanCount',IFNULL(e.PSNCOUNT,0) 'psnCount' ," +
                " IFNULL(e.SCANCOUNT_TOTAL,0)  'history_scan_total', " +
                " IFNULL(e.WINCOUNT, 0) 'winCount', IFNULL(e.ENTITYCOUNT,0) 'entityCount',IFNULL(e.REDPACKCOUNT,0) 'redCount' from" +
                " (SELECT a.year,a.month, left(a.vdate,6) 'vdate1' from dim_calendar a  " );

        StringBuffer sqlStastic1= new StringBuffer(" group by a.year,a.month )a  left join");
        if (productId != null && productId != "" && !productId.equals("null")) {
            sqlStastic1.append(" ETL_FACT_MONTHLY e on e.YEARMONTH = a.vdate1  and e.PRODUCT_ID = " + productId);
        }else{
            sqlStastic1.append(" ETL_CORP_MONTHLY e on e.YEARMONTH = a.vdate1 ");
        }
        sqlStastic1.append("  and e.COMPANY_ID =" + companyId  +" )p join");

        //新增用户
        StringBuffer sqlStastic2= new StringBuffer(" (SELECT a.year, a.month, sum(IFNULL(c.PSNADDNUM,0) )scanpan FROM dim_calendar a left join ");
        if (productId != null && productId != "" && !productId.equals("null")) {
            sqlStastic2.append(" ETL_WEIXIN_PSNSUMBYPROD c on a.vdate=c.VDATE  and c.PRODUCT_ID = " + productId);
        }else{
            sqlStastic2.append(" ETL_WEIXIN_PSNSUMBYCORP c on a.vdate=c.VDATE ");
        }
        sqlStastic2.append("  and c.COMPANY_ID =" + companyId  );


        StringBuffer step2_sqlWhere = new StringBuffer("  where 1=1");
        if (timeLimitStart != null) {
            step2_sqlWhere.append(" and a.vdate >= " + timeLimitStart);
        }
        if (timeLimitEnd != null) {
            step2_sqlWhere.append(" and a.vdate <= " + timeLimitEnd);
        }

        StringBuffer str = sqlStastic.append(step2_sqlWhere).append(sqlStastic1).append(sqlStastic2).append(step2_sqlWhere).append(" group by a.year, a.month )n on p.year = n.year and p.month=n.month order by p.vdate1");
        List<Map<String,Object>> result = jdbcTemplate.queryForList(str.toString());
        return  result;

    }

    /**
     * 根据区域统计 柱形图
     * @param productId
     * @return
     */
    public List<Map<String,Object>> getAreaData(Integer companyId,String productId, Long scanStartTime){
        StringBuffer sqlStastic = new StringBuffer("select a.poi_city, a.scanData, ifnull(b.openDate,0) 'openDate' from " +
                "(select  ac.poi_city ,count(*) 'scanData' from  activity_winning ac join product p"+
                " on p.id=ac.product_id  where ac.poi_city is not null ");
        StringBuffer sqlStastic2 = new StringBuffer(" group by ac.poi_city order by scanData desc limit 10)a" +
                " left join (select count(distinct ac.weixin_openid) 'openDate',ac.poi_city, count(*) from activity_winning ac join product p"+
                " on p.id=ac.product_id where" +
                "  ac.poi_city is not null  and ac.weixin_openid is not null and ac.weixin_openid != '' ");
        StringBuffer sqlStastic3=new StringBuffer(" group by ac.poi_city)b on a.poi_city = b.poi_city");

        StringBuffer step2_sqlWhere = new StringBuffer(" and  ac.scan_time>"+ scanStartTime+" and p.company_id=" + companyId);

        if (productId != null && !productId.equals("")) {
            step2_sqlWhere.append(" and ac.product_id = " + productId);
        }

        StringBuffer str =sqlStastic.append(step2_sqlWhere).append(sqlStastic2).append(step2_sqlWhere).append(sqlStastic3);
        List<Map<String,Object>> result = jdbcTemplate.queryForList(str.toString());
        return result;

    }

    /**
     * 根据区域统计列表详情
     * @param productId
     * @return
     */
    public Pageable<Map<String,Object>> getAreaDataDetail(Integer companyId,String productId,Integer page,Long scanStartTime){

        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from (select  count(*) from  activity_winning ac join product p on p.id=ac.product_id where ac.poi_city is not null");
        StringBuffer sqlStastic = new StringBuffer("select a.poi_city, a.scanData, ifnull(b.openDate,0) 'openDate',ifnull(c.prizeData,0) 'prizeData',ifnull(d.redData,0) 'redData'" +
                " from (select  ac.poi_city ,count(*) 'scanData' from  activity_winning ac join product p on p.id=ac.product_id" +
                " where ac.poi_city is not null ");
        StringBuffer sqlStastic2 = new StringBuffer(" group by ac.poi_city order by scanData desc)a" +
                " left join (select count(distinct weixin_openid) 'openDate',ac.poi_city, count(*) from activity_winning ac join product p on p.id=ac.product_id where" +
                "  ac.poi_city is not null  and ac.weixin_openid is not null and ac.weixin_openid != '' ");
        StringBuffer sqlStastic3=new StringBuffer(" group by ac.poi_city)b on a.poi_city = b.poi_city left join " +
                " (select  ac.poi_city ,count(*) 'prizeData' from  activity_winning ac join product p on p.id=ac.product_id join prize pr on pr.id = ac.prize_id" +
                " where ac.poi_city is not null and weixin_openid is not null and weixin_openid != ''");
        StringBuffer sqlStastic4=new StringBuffer(" group by ac.poi_city)c on c.poi_city=a.poi_city  left join " +
                " (select  ac.poi_city ,count(*) 'redData' from  activity_winning ac join product p on p.id=ac.product_id join prize pr on pr.id = ac.prize_id" +
                " where ac.poi_city is not null and weixin_openid is not null and weixin_openid != '' and pr.type=1");

        StringBuffer sqlStastic5=new StringBuffer(" group by ac.poi_city ) d on d.poi_city =a.poi_city");
        StringBuffer step2_sqlWhere = new StringBuffer(" and  ac.scan_time>"+ scanStartTime+" and p.company_id=" + companyId);

        if (productId != null && !productId .equals("") && !productId.equals("null")) {
            step2_sqlWhere.append(" and ac.product_id = " + productId);
        }

        StringBuffer str = sqlStastic.append(step2_sqlWhere).append(sqlStastic2).append(step2_sqlWhere).append(sqlStastic3).append(step2_sqlWhere).append(sqlStastic4).append(step2_sqlWhere).append(sqlStastic5).append(" limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());

        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(step2_sqlWhere + " group by ac.poi_city)a").toString(), Integer.class);

        List<Map<String,Object>> result = jdbcTemplate.queryForList(str.toString(),args);
        return  new Pageable<>(page, getPageSize(), resultTotal, result);

    }

    /**
     * 区域分布数据导出
     * @param productId
     * @return
     */
    public List<Map<String,Object>> getAreaDataDetailExport(Integer companyId,String productId,Long scanStartTime){

        StringBuffer sqlStastic = new StringBuffer("select a.poi_city, a.scanData, ifnull(b.openDate,0) 'openDate',ifnull(c.prizeData,0) 'prizeData',ifnull(d.redData,0) 'redData'" +
                " from (select  ac.poi_city ,count(*) 'scanData' from  activity_winning ac join product p on p.id=ac.product_id" +
                " where ac.poi_city is not null ");
        StringBuffer sqlStastic2 = new StringBuffer(" group by ac.poi_city order by scanData desc)a" +
                " left join (select count(distinct weixin_openid) 'openDate',ac.poi_city, count(*) from activity_winning ac join product p on p.id=ac.product_id where" +
                "  ac.poi_city is not null  and ac.weixin_openid is not null and ac.weixin_openid != '' ");
        StringBuffer sqlStastic3=new StringBuffer(" group by ac.poi_city)b on a.poi_city = b.poi_city left join " +
                " (select  ac.poi_city ,count(*) 'prizeData' from  activity_winning ac join product p on p.id=ac.product_id join prize pr on pr.id = ac.prize_id" +
                " where ac.poi_city is not null and weixin_openid is not null and weixin_openid != ''");
        StringBuffer sqlStastic4=new StringBuffer(" group by ac.poi_city)c on c.poi_city=a.poi_city  left join " +
                " (select  ac.poi_city ,count(*) 'redData' from  activity_winning ac join product p on p.id=ac.product_id join prize pr on pr.id = ac.prize_id" +
                " where ac.poi_city is not null and weixin_openid is not null and weixin_openid != '' and pr.type=1");

        StringBuffer sqlStastic5=new StringBuffer(" group by ac.poi_city ) d on d.poi_city =a.poi_city");
        StringBuffer step2_sqlWhere = new StringBuffer("  and  ac.scan_time>"+ scanStartTime+" and p.company_id=" + companyId);

        if (productId != null && !productId .equals("") && !productId.equals("null")) {
            step2_sqlWhere.append(" and ac.product_id = " + productId);
        }

        StringBuffer str = sqlStastic.append(step2_sqlWhere).append(sqlStastic2).append(step2_sqlWhere).append(sqlStastic3).append(step2_sqlWhere).append(sqlStastic4).append(step2_sqlWhere).append(sqlStastic5);

        List<Map<String,Object>> result = jdbcTemplate.queryForList(str.toString());
        return  result;

    }
    
    /**
     * 时段分布 获取x轴展示数组
     * @author kelan
     * @date 2017年8月16日
     * @return
     */
    public List<Map<String,Object>> getxAxisTime (){
    	String sql = "select CONCAT(begintime,'-',endtime) xtime from dim_time " ;
    	List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
    	return list;
    }


    /**
     * 扫描量/用户量 按小时显示
     * @param productId
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public  List<Map<String,Object>> dataCountByHour(Integer companyId, String productId, String timeLimitStart, String timeLimitEnd) {


        StringBuffer sqlStastic = new StringBuffer("select d.hour, IFNULL(a.SCANCOUNT,0) 'SCANCOUNT'," +
                "IFNULL(b.scanpsn,0) 'scanpsn' from dim_time d left join" +
                " ( select c.HOUR, sum(c.SCANCOUNT) 'SCANCOUNT',sum(c.psn_count) 'scanpsn' " +
                "FROM ETL_FACT_QRSCAN c join product p on p.id=c.PRODUCT_ID" );

        //用户量
        StringBuffer sqlStastic1 = new StringBuffer(" group by c.HOUR) a on  a.HOUR = d.hour left join " +
                "(select  c.HOUR, count(distinct c.WEIXIN_OPENID) 'scanpsn' from ETL_DETAIL_QRSCAN c join product p on p.id=c.PRODUCT_ID");



        StringBuffer step2_sqlWhere = new StringBuffer("  where  p.company_id =" + companyId);
        if (productId != null && productId != "") {
            step2_sqlWhere.append(" and c.PRODUCT_ID = " + productId);
        }

        if (timeLimitStart != null) {
            step2_sqlWhere.append(" and c.vdate >= " + timeLimitStart);
        }
        if (timeLimitEnd != null) {
            step2_sqlWhere.append(" and c.vdate <= " + timeLimitEnd);
        }

        // 查询统计数据
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlStastic.append(step2_sqlWhere).append(sqlStastic1).append(step2_sqlWhere).append(" group by c.HOUR) b on b.HOUR = d.hour order by d.hour").toString());
        return result;
    }
    
    /**
     * 时段分布  按小时列表数据查询显示
     * @param productId
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public  Pageable<Map<String,Object>> getlistDataByHour(Integer companyId, String productId, String timeLimitStart, String timeLimitEnd,Integer page) {

        Object[] step2_args = {};
        StringBuffer sqlStasticcount =  new StringBuffer("select count(*) from ( ");
        StringBuffer sqlStastic = new StringBuffer("select d.hour 'HOUR', CONCAT(d.begintime,'-',d.endtime) xtime,IFNULL(b.scanpsn,0) 'scanpsn', " +
                "IFNULL(a.SCANCOUNT,0) 'SCANCOUNT',IFNULL(a.WINCOUNT,0) 'WINCOUNT', "
        		+ " IFNULL(a.ENTITYCOUNT,0) 'ENTITYCOUNT',IFNULL(a.REDPACKCOUNT,0) 'REDPACKCOUNT' from dim_time d left join "
        		+ " ( select c.HOUR, sum(c.psn_count) 'scanpsn',sum(c.SCANCOUNT) 'SCANCOUNT',sum(c.WIN_COUNT) 'WINCOUNT',"
        		+ "  sum(c.ENTITY_COUNT) 'ENTITYCOUNT',sum(c.REDPACK_COUNT) 'REDPACKCOUNT' " +
                " FROM ETL_FACT_QRSCAN c join product p on p.id=c.PRODUCT_ID ");

        //用户量
        StringBuffer sqlStastic1 = new StringBuffer(" group by c.HOUR) a on  a.HOUR = d.hour left join " +
                "(select  c.HOUR, count(distinct c.WEIXIN_OPENID) 'scanpsn' from ETL_DETAIL_QRSCAN c join product p on p.id=c.PRODUCT_ID");


        StringBuffer step2_sqlWhere = new StringBuffer(" where p.company_id =" + companyId);

        if (productId != null && productId != "" && !productId.equals("null")) {
            step2_sqlWhere.append(" and c.PRODUCT_ID = " + productId);
        }


        if (timeLimitStart != null) {
            step2_sqlWhere.append(" and c.vdate >= " + timeLimitStart);
        }
        if (timeLimitEnd != null) {
            step2_sqlWhere.append(" and c.vdate <= " + timeLimitEnd);
        }

        sqlStastic.append(step2_sqlWhere).append(sqlStastic1).append(step2_sqlWhere).append(" group by c.HOUR) b on b.HOUR = d.hour order by d.hour");

        sqlStasticcount.append(sqlStastic).append(" )bb" );
        Integer resultTotal = jdbcTemplate.queryForObject(sqlStasticcount.toString(), Integer.class);
        sqlStastic.append(" limit ?,?");
        step2_args = ArrayUtils.add(step2_args, (page - 1) * getPageSize());
        step2_args = ArrayUtils.add(step2_args, getPageSize());
        // 查询统计数据
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlStastic.toString(), step2_args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }
    
    /**
    * 时段分布  按小时列表数据导出
    * @param productId
    * @param timeLimitStart
    * @param timeLimitEnd
    * @return
    */
   public  List<Map<String,Object>> getlistDataByHourExport(Integer companyId, String productId, String timeLimitStart, String timeLimitEnd) {

       StringBuffer sqlStastic = new StringBuffer("select d.hour 'HOUR', CONCAT(d.begintime,'-',d.endtime) xtime,IFNULL(b.scanpsn,0) 'scanpsn', " +
               "IFNULL(a.SCANCOUNT,0) 'SCANCOUNT',IFNULL(a.WINCOUNT,0) 'WINCOUNT', "
               + " IFNULL(a.ENTITYCOUNT,0) 'ENTITYCOUNT',IFNULL(a.REDPACKCOUNT,0) 'REDPACKCOUNT' from dim_time d left join "
               + " ( select c.HOUR, sum(c.psn_count) 'scanpsn',sum(c.SCANCOUNT) 'SCANCOUNT',sum(c.WIN_COUNT) 'WINCOUNT',"
               + "  sum(c.ENTITY_COUNT) 'ENTITYCOUNT',sum(c.REDPACK_COUNT) 'REDPACKCOUNT' " +
               " FROM ETL_FACT_QRSCAN c join product p on p.id=c.PRODUCT_ID ");

       //用户量
       StringBuffer sqlStastic1 = new StringBuffer(" group by c.HOUR) a on  a.HOUR = d.hour left join " +
               "(select  c.HOUR, count(distinct c.WEIXIN_OPENID) 'scanpsn' from ETL_DETAIL_QRSCAN c join product p on p.id=c.PRODUCT_ID");


       StringBuffer step2_sqlWhere = new StringBuffer(" where p.company_id =" + companyId);

       if (productId != null && productId != "") {
           step2_sqlWhere.append(" and c.PRODUCT_ID = " + productId);
       }


       if (timeLimitStart != null) {
           step2_sqlWhere.append(" and c.vdate >= " + timeLimitStart);
       }
       if (timeLimitEnd != null) {
           step2_sqlWhere.append(" and c.vdate <= " + timeLimitEnd);
       }

       sqlStastic.append(step2_sqlWhere).append(sqlStastic1).append(step2_sqlWhere).append(" group by c.HOUR) b on b.HOUR = d.hour order by d.hour");

       // 查询统计数据
       List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlStastic.toString());
       return result;
   }
    
}
